﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Xml;
using System.IO;
using System.Collections;
using System.Collections.Generic;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;

using NPOI.SS.UserModel;


namespace NetDon.eBoss.Portal
{
    /// <summary>
    /// Excel导出导入帮助类
    /// </summary>
    public class ExcelHelper
    {
        public static class ExcelCellStyle
        {
            public const string Style1 = "Style1";
            public const string Style2 = "Style2";
            public const string Style3 = "Style3";
            public const string Style4 = "Style4";
            public const string Style5 = "Style5";
        }
        /// <summary>
        /// 样式模板
        /// </summary>
        public static Dictionary<string, CellStyle> CellStyles
        {
            get;
            private set;
        }

        public static Dictionary<string, CellStyle> CurrentWorkbookCellStyles
        {
            get;
            private set;
        }

        static ExcelHelper()
        {
            CellStyles = new Dictionary<string, CellStyle>();
            string path = string.Empty;

            path = AppDomain.CurrentDomain.BaseDirectory;
            using (FileStream file = new FileStream(Path.Combine(path, "ExcelStyle.xls"), FileMode.Open, FileAccess.Read))
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                Sheet s = hssfworkbook.GetSheetAt(0);
                for (int i = s.FirstRowNum; i <= s.LastRowNum; i++)
                {
                    Cell c0 = s.GetRow(i).GetCell(0);
                    Cell c1 = s.GetRow(i).GetCell(1);
                    CellStyles.Add(c0.StringCellValue, c1.CellStyle);
                    if (c0.StringCellValue == "DateTime")
                    {
                        DataFormat format = hssfworkbook.CreateDataFormat();
                        CellStyles["DateTime"].DataFormat = format.GetFormat(c1.StringCellValue);
                    }
                }
            }
        }

        public static byte[] Export(DataTable dt)
        {
            return Export(dt, 28 * 256);
        }

        public static byte[] Export(DataTable dt, int defaultColumnWidth)
        {
            HSSFWorkbook workbook = CreateWorkbook();

            CreateCurrentWorkbookStyles(workbook);

            Sheet sheet = workbook.CreateSheet("Sheet1");
            WriteTable(workbook, sheet, dt);
            Row row = sheet.GetRow(0);
            for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
            {
                sheet.SetColumnWidth(i, defaultColumnWidth);
            }

            return Render(workbook);
        }

        /// <summary>
        /// 创建用于当前工作簿的样式
        /// </summary>
        /// <param name="workbook"></param>
        private static void CreateCurrentWorkbookStyles(HSSFWorkbook workbook)
        {
            CurrentWorkbookCellStyles = new Dictionary<string, CellStyle>();
            foreach (string csKey in CellStyles.Keys)
            {
                CellStyle csNew = workbook.CreateCellStyle();
                csNew.CloneStyleFrom(CellStyles[csKey]);
                CurrentWorkbookCellStyles.Add(csKey, csNew);
            }
        }

        /// <summary>
        /// 输出成二进制
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        public static byte[] Render(HSSFWorkbook workbook)
        {
            MemoryStream stream = new MemoryStream();
            workbook.Write(stream);
            return stream.ToArray();
        }

        /// <summary>
        /// 创建excel workbook
        /// </summary>
        /// <returns></returns>
        public static HSSFWorkbook CreateWorkbook()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "VSA";
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "VSA System";
            workbook.DocumentSummaryInformation = dsi;
            workbook.SummaryInformation = si;
            return workbook;
        }

        public static HSSFWorkbook CopyWorkbook(FileStream file)
        {
            //FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read);

            HSSFWorkbook workbook = new HSSFWorkbook(file);
            CreateCurrentWorkbookStyles(workbook);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "VSA";
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "VSA System";
            workbook.SummaryInformation = si;
            return workbook;

        }

        /// <summary>
        /// 设置本次导出基本信息. Add by shakken xie on 2010-5-19
        /// </summary>
        /// <param name="wookBook">HSSFWorkbook 实例</param>
        /// <param name="projectId">当前ProjectId</param>
        /// <param name="userRole">当前用户RoleId</param>
        /// <param name="planInfoList">PlanId|PlanType值|PlanStatus值 列表</param>
        public static void WriteCurstomerCustomProperties(HSSFWorkbook wookBook, string projectId, string userRole, List<string> planInfoList)
        {
            string result = string.Empty;
            result = projectId + "\r\n" + userRole + "\r\n";
            foreach (string item in planInfoList)
            {
                result += item + "\r\n";
            }
            result = result.Substring(0, result.Length - 2);
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "CRM System";
            si.Comments = result;
            wookBook.SummaryInformation = si;
        }

        /// <summary>
        /// 创建表头样式, Add by shakken xie on 2010-5-19
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="row"></param>
        public static void SetTableHeader(HSSFWorkbook workbook, int rowIndex)
        {
            CellStyle style1 = workbook.CreateCellStyle();
            style1.CloneStyleFrom(CellStyles[ExcelCellStyle.Style1]);
            Row row = workbook.GetSheet("Sheet1").GetRow(rowIndex);
            for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
            {
                row.GetCell(i).CellStyle = style1;
            }
        }

     
        /// <summary>
        /// 解锁
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="columnIndex"></param>
        public static void UnlockColumn(HSSFWorkbook workbook, HSSFSheet sheet, int columnIndex)
        {
            CellStyle unlocked = workbook.CreateCellStyle();
            unlocked.IsLocked = false;
            for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
            {
                sheet.GetRow(i).GetCell(columnIndex).CellStyle = unlocked;
            }
        }

        /// <summary>
        /// 解锁
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="columnIndex"></param>
        public static void UnlockCell(HSSFWorkbook workbook, Sheet sheet, int rowIndex, int columnIndex)
        {
            CellStyle unlocked = workbook.CreateCellStyle();
            unlocked.IsLocked = false;
            sheet.GetRow(rowIndex).GetCell(columnIndex).CellStyle = unlocked;
        }

        /// <summary>
        /// 往sheet写入table
        /// </summary>
        public static void WriteTable(HSSFWorkbook workbook, Sheet sheet, DataTable dt)
        {
            WriteTable(workbook, sheet, dt, 0);
        }

        /// <summary>
        /// 往sheet写入table
        /// </summary>
        public static void WriteTable(HSSFWorkbook workbook, Sheet sheet, DataTable dt, int rowIndex)
        {
            WriteTable(workbook, sheet, dt, rowIndex, 0);
        }

        /// <summary>
        /// 往sheet写入table
        /// </summary>
        public static void WriteTable(HSSFWorkbook workbook, Sheet sheet, DataTable dt, int rowIndex, int columnIndex)
        {
            WriteTable(workbook, sheet, dt, rowIndex, columnIndex, true, false);
        }

        /// <summary>
        /// 往sheet写入table
        /// </summary>
        /// <param name="sheet">表单</param>
        /// <param name="dt">数据源</param>
        /// <param name="rowIndex">行号</param>
        /// <param name="columnIndex">列号</param>
        /// <param name="writeHeader">是否写入表头</param>
        /// <param name="useCaption">若写入表头，是否使用Caption</param>
        public static void WriteTable(HSSFWorkbook workbook, Sheet sheet, DataTable dt, int rowIndex, int columnIndex, bool writeHeader, bool useCaption)
        {
            string[] header = new string[dt.Columns.Count];
            for (int i = 0; i < header.Length; i++)
            {
                if (useCaption)
                {
                    header[i] = dt.Columns[i].Caption;
                }
                else
                {
                    header[i] = dt.Columns[i].ColumnName;
                }
            }
            if (writeHeader)
            {
                CellStyle style1 = workbook.CreateCellStyle();
                style1.CloneStyleFrom(CellStyles[ExcelCellStyle.Style1]);
                Row row = WriteRow(sheet, header, rowIndex, columnIndex);
                for (int i = row.FirstCellNum; i < row.LastCellNum; i++)
                {
                    row.GetCell(i).CellStyle = style1;
                }
            }
            rowIndex++;
            CellStyle style2 = workbook.CreateCellStyle();
            style2.CloneStyleFrom(CellStyles[ExcelCellStyle.Style2]);
            foreach (DataRow dr in dt.Rows)
            {
                Row row = WriteRow(sheet, dr.ItemArray, rowIndex, columnIndex);

                rowIndex++;
            }
            //sheet.DisplayGridlines = false;
            //for (int i = columnIndex; i < columnIndex+dt.Columns.Count; i++)
            //{
            //    sheet.AutoSizeColumn(i, true);
            //}
        }

        /// <summary>
        /// 写入行
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="dr"></param>
        /// <param name="rowIndex"></param>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        public static Row WriteRow(Sheet sheet, DataRow dr, int rowIndex, int columnIndex)
        {
            return WriteRow(sheet, dr.ItemArray, rowIndex, columnIndex);
        }

        /// <summary>
        /// 写入行
        /// </summary>
        public static Row WriteRow(Sheet sheet, object[] dr, int rowIndex, int columnIndex)
        {
            Row row = sheet.CreateRow(rowIndex);
            int i = 0;
            while (i < dr.Length)
            {
                WriteCell(row, dr[i], i);
                i++;
                columnIndex++;
            }
            return row;
        }

        /// <summary>
        /// 写入单元格
        /// </summary>
        public static Cell WriteCell(Row row, object obj, int columnIndex)
        {
            Cell cell = row.GetCell(columnIndex);
            if (cell == null)
                cell = row.CreateCell(columnIndex);
            if (obj is string)
            {
                cell.SetCellValue((string)obj);
            }
            else if (obj is decimal)
            {
                cell.SetCellValue(Convert.ToDouble(obj));
            }
            else if (obj is DateTime)
            {
                cell.SetCellValue((DateTime)obj);
                if (CurrentWorkbookCellStyles.ContainsKey("DateTime"))
                {
                    cell.CellStyle = CurrentWorkbookCellStyles["DateTime"];
                }
            }
            else if (obj is bool)
            {
                cell.SetCellValue(obj.ToString());
            }
            else if (obj.GetType().IsValueType)
            {
                cell.SetCellValue(Convert.ToDouble(obj));
            }
            else
            {
                cell.SetCellValue(obj.ToString());
            }
            return cell;
        }

        /// <summary>
        /// 往单元格写数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="columnIndex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static Cell WriteCell(Sheet sheet, int columnIndex, int rowIndex, object obj)
        {
            Row row = sheet.GetRow(rowIndex);
            if (row == null)
            {
                row = sheet.CreateRow(rowIndex);
            }
            return WriteCell(row, obj, columnIndex);
        }

   
        public static object GetValue(HSSFCell cell)
        {
            switch (cell.CellType)
            {
                case CellType.BLANK: //BLANK:
                    return null;
                case CellType.BOOLEAN: //BOOLEAN:
                    return cell.BooleanCellValue;
                case CellType.NUMERIC: //NUMERIC:
                    return cell.NumericCellValue;
                case CellType.STRING: //STRING:
                    return cell.StringCellValue;
                case CellType.ERROR: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.FORMULA: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
    }

}
